﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using WinNemoLib;
using System.Data.SqlClient;
using System.Text;
using System.Data;

namespace TireBias.Models
{
   public class BIASPLANModel
   {
      public class BIASPLAN_DS
      {
         public string _Biasno { get; set; }
         public string _Plandt { get; set; }
         public string _Plants { get; set; }
         public string _Itemno { get; set; }
         public string _Topcrdcd { get; set; }
         public string _Size { get; set; }
         public string _P1 { get; set; }
         public string _P2 { get; set; }
         public string _P3 { get; set; }
         public string _P4 { get; set; }
         public string _Text { get; set; }
         public string _Forming { get; set; }
         public string _UpTemp { get; set; }
         public string _Uptdat { get; set; }
         public string _Actflag { get; set; }
         public string _Actdt { get; set; }
         public string _Runflag { get; set; }
         public string _Rundt { get; set; }
         public string _Empname { get; set; }
         public string _Shift { get; set; }
         public string _Finflag { get; set; }
         public string _Findt { get; set; }
         public string _toproll { get; set; }
         public string _tcrdlot { get; set; }
         public string _topexp { get; set; }
         public string _toplen { get; set; }
         public string cP1 { get; set; }
         public string cP2 { get; set; }
         public string cP3 { get; set; }
         public string cP4 { get; set; }
         public string _Sample { get; set; }
         public string _Smpemp { get; set; }
         public string _Smpshtxt { get; set; }
         public string _topauthemp { get; set; }
         public string _topreatxt { get; set; }
         public string _topshtxt { get; set; }
         public string _Skpflag { get; set; }
         public string _OldSeq { get; set; }
         public string _nfinemp { get; set; }
         public string _Angel { get; set; }
      }

      public List<BIASPLAN_DS> SEL_UNLOCK(string plandt,string biasno)
      {
       
         string ConnectionString = Nemo.GetConfiguredDbConnectionWrappertostring("Database");
         SqlConnection cnn = new SqlConnection(ConnectionString);
         StringBuilder str = new StringBuilder();

         str.AppendLine(" select * ");        
         str.AppendLine(" from " + Nemo.GetINI("Dbase", "Base") + ".dbo.BIASPLAN t");

         str.AppendLine(" where plandt=convert(datetime,@plndt,120) and biasno=@biasno and actflag is null");


         SqlCommand cmd = new SqlCommand(str.ToString() + " order by plandt,convert(int,itemno) ", cnn);
         cmd.Connection.Open();
         cmd.CommandType = CommandType.Text;


         cmd.Parameters.Add("@plndt", SqlDbType.VarChar).Value = DateUtil.ToInternalDate(plandt, WinNemoLib.DateUtil.eLanguage.EN);
         cmd.Parameters.Add("@biasno", SqlDbType.VarChar).Value = biasno;

         SqlDataAdapter my = new SqlDataAdapter();
         DataSet ds = new DataSet();
         my.SelectCommand = cmd;
         my.Fill(ds);

         List<BIASPLAN_DS> list = new List<BIASPLAN_DS>();
         if (ds.Tables[0].Rows.Count <= 0)
         {
            cmd.Connection.Close();
            return list;
         }
         for (int cuIndex = 0; cuIndex < ds.Tables[0].Rows.Count; cuIndex++)
         {
            DataRow items = ds.Tables[0].Rows[cuIndex];
            BIASPLAN_DS model = new BIASPLAN_DS();
            model._Biasno = items["biasno"].ToString();
            model._Itemno = items["itemno"].ToString();
            model._Topcrdcd = items["topcrdcd"].ToString();
            model._Size = items["sizes"].ToString();
            model._Forming = items["Forming"].ToString();
            model._Plants = items["Plansts"].ToString();
            model._Plandt = DateUtil.ToDisplayDate(items["plandt"], DateUtil.eLanguage.EN);
            model._P1 = items["P1"].ToString();
            model._P2 = items["P2"].ToString();
            model._P3 = items["P3"].ToString();
            model._P4 = items["P4"].ToString();
            model._Uptdat = DateUtil.ToDisplayDateTime(items["Uptdat"], DateUtil.eLanguage.EN);
            model._UpTemp = items["Uptemp"].ToString();
            model._Text = items["text"].ToString();
            model._Skpflag = items["skpflag"].ToString();
            list.Add(model);
         }
         return list;
      }

      public int GET_SEQ(string plandt, string biasno, string itemno = "", Boolean isSample = false)
      {
         int seq = 0;
         string ConnectionString = Nemo.GetConfiguredDbConnectionWrappertostring("Database");
         SqlConnection cnn = new SqlConnection(ConnectionString);
         StringBuilder str = new StringBuilder();

         if (isSample)
         {
             str.AppendLine("SELECT isnull(max(convert(int,itemno)),0)+1 seq_no from " + Nemo.GetINI("Dbase", "Base") + ".dbo.BIASPLAN");
             str.AppendLine(" where  plandt=convert(datetime,@dTo,120)  AND ITEMNO BETWEEN CONVERT(INT,@It)-10 AND  CONVERT(INT,@It)-1 ");
             //str.AppendLine(" and Skpflag is null ");
             str.AppendLine(" group by plandt,biasno ");
         }
         else
         {
             str.AppendLine(" select isnull(max(convert(int,itemno)),0)+10 seq_no from " + Nemo.GetINI("Dbase", "Base") + ".dbo.BIASPLAN ");
             str.AppendLine(" where plansts ='L' and plandt=convert(datetime,@dTo,120) and biasno=@biasno ");
             str.AppendLine(" group by plandt,biasno ");
         }
         SqlCommand cmd = new SqlCommand(str.ToString(), cnn);
         cmd.Connection.Open();
         cmd.CommandType = CommandType.Text;
         cmd.Parameters.Add("@dTo", SqlDbType.NVarChar).Value = DateUtil.ToInternalDate(plandt, WinNemoLib.DateUtil.eLanguage.EN);
         cmd.Parameters.Add("@biasno", SqlDbType.VarChar).Value = biasno;
         if (isSample)
         {
             cmd.Parameters.Add("@It", SqlDbType.VarChar).Value = itemno;
         }

         SqlDataAdapter my = new SqlDataAdapter();
         DataSet ds = new DataSet();
         my.SelectCommand = cmd;
         my.Fill(ds);

         List<BIASPLAN_DS> list = new List<BIASPLAN_DS>();
         if (ds.Tables[0].Rows.Count <= 0)
         {
            cmd.Connection.Close();
            return 10;
         }
         seq = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
         return seq;
      }

      //public int GET_SEQ(string plndt, string itemno = "", Boolean isSample = false)
      //{
      //    int seq = 0;
      //    string ConnectionString = Nemo.GetConfiguredDbConnectionWrappertostring("Database");
      //    SqlConnection cnn = new SqlConnection(ConnectionString);
      //    StringBuilder str = new StringBuilder();

      //    if (isSample)
      //    {
      //        str.AppendLine("SELECT ISNULL(MAX(ITEMNO),0)+1 seq_no from " + Nemo.GetINI("Dbase", "Base") + ".dbo.TOPCORDPLAN ");
      //        str.AppendLine(" where  plandt=convert(datetime,@dTo,120)  AND ITEMNO BETWEEN CONVERT(INT,@It)-10 AND  CONVERT(INT,@It)-1 ");
      //        //str.AppendLine(" and Skpflag is null ");
      //        str.AppendLine(" group by plandt ");
      //    }
      //    else
      //    {
      //        str.AppendLine(" select isnull(max(itemno),0)+10 seq_no from " + Nemo.GetINI("Dbase", "Base") + ".dbo.TOPCORDPLAN ");
      //        str.AppendLine(" where (plansts ='L' and plandt=convert(datetime,@dTo,120))  ");
      //        str.AppendLine(" and Skpflag is null ");
      //        str.AppendLine(" group by plandt ");
      //    }

      //    SqlCommand cmd = new SqlCommand(str.ToString(), cnn);
      //    cmd.Connection.Open();
      //    cmd.CommandType = CommandType.Text;
      //    cmd.Parameters.Add("@dTo", SqlDbType.NVarChar).Value = DateUtil.ToInternalDate(plndt, WinNemoLib.DateUtil.eLanguage.EN);
      //    if (isSample)
      //    {
      //        cmd.Parameters.Add("@It", SqlDbType.NVarChar).Value = itemno;
      //    }
      //    SqlDataAdapter my = new SqlDataAdapter();
      //    DataSet ds = new DataSet();
      //    my.SelectCommand = cmd;
      //    my.Fill(ds);

      //    List<TopCordPlan_DS> list = new List<TopCordPlan_DS>();
      //    if (ds.Tables[0].Rows.Count <= 0)
      //    {
      //        cmd.Connection.Close();
      //        return 10;
      //    }
      //    seq = Convert.ToInt32(ds.Tables[0].Rows[0][0]);

      //    return seq;
      //}

      public Boolean DEL_DATA_NO_ACTIVE(ConnectionWrapper cnn,string dtPlan,string biasno)
      {
         try
         {
            StringBuilder comstr = new StringBuilder();
            comstr.AppendLine(" DELETE ");
            comstr.AppendLine(Nemo.GetINI("Dbase", "Base") + ".[dbo].[BIASPLAN]");
            comstr.AppendLine("where Plansts='U'  and plandt=convert(datetime,@plndt,120) and biasno=@biasno ");

            CommandWrapper cmd = cnn.CreateCommand();
            cmd.CommandText = comstr.ToString();
            cmd.AddParameterStringWithValue("@plndt", DateUtil.ToInternalDate(dtPlan, WinNemoLib.DateUtil.eLanguage.EN));
            cmd.AddParameterStringWithValue("@biasno",biasno);
            cmd.ExecuteNonQuery();

            return true;
         }
         catch (Exception ex)
         {
            return false;
            throw ex;
         }

      }


      public Boolean Ins_Data(ConnectionWrapper cnn, BIASPLAN_DS incoming, string usernm)
      {
         try
         {
            StringBuilder comstr = new StringBuilder();
            comstr.AppendLine(" INSERT INTO ");
            comstr.AppendLine(Nemo.GetINI("Dbase", "Base") + ".[dbo].[BIASPLAN]");
            comstr.AppendLine(" (biasno,Plansts");
            if (StrUtil.IsNotEmpty(incoming._Sample))
            {// sample
                comstr.AppendLine(",actflag,actdt ");
                comstr.AppendLine(",[toproll], [Tcrdlot]");
                comstr.AppendLine(", [topexp], [toplen],smpemp,smpshtxt,shift,topauthemp,topreatxt,topshtxt");
             
            }
            comstr.AppendLine(",plandt,itemno,topcrdcd, [sizes], [forming], [P1], [P2], [P3], [P4],[Text], [Uptemp], [Uptdat] ) VALUES ( @topno ,@status ");
            if (StrUtil.IsNotEmpty(incoming._Sample))
            {
                comstr.AppendLine(",'X',getdate() ");
                comstr.AppendLine(",@toproll,@Tcrdlot");
                comstr.AppendLine(",convert(datetime,@topexp,120) , @toplen ,@smpemp,@smpshtxt,@shift ,@topauthemp,@topreatxt,@topshtxt");
      
            }
            comstr.AppendLine(" , convert(datetime,@plndt,120),@itemno,UPPER(@topcode), UPPER(@sizes), @forming, @P1, @P2, @P3, @P4,@text, @ModifyBy, getdate() ) ");

            CommandWrapper cmd = cnn.CreateCommand();
            cmd.CommandText = comstr.ToString();


            cmd.AddParameterStringWithValue("@topno", incoming._Biasno.Trim());
            cmd.AddParameterStringWithValue("@plndt", DateUtil.ToInternalDate(incoming._Plandt, WinNemoLib.DateUtil.eLanguage.EN));
            cmd.AddParameterStringWithValue("@status", incoming._Plants);
            cmd.AddParameterStringWithValue("@itemno", incoming._Itemno);
            cmd.AddParameterStringWithValue("@topcode", incoming._Topcrdcd);
            cmd.AddParameterStringWithValue("@sizes", incoming._Size);
            cmd.AddParameterStringWithValue("@forming", incoming._Forming);
            cmd.AddParameterStringWithValue("@P1", incoming._P1);
            cmd.AddParameterStringWithValue("@P2", incoming._P2);
            cmd.AddParameterStringWithValue("@P3", incoming._P3);
            cmd.AddParameterStringWithValue("@P4", incoming._P4);
            cmd.AddParameterStringWithValue("@Text", incoming._Text);

            if (StrUtil.IsNotEmpty(incoming._Sample))
            {                
                cmd.AddParameterStringWithValue("@toproll", incoming._toproll);
                cmd.AddParameterStringWithValue("@Tcrdlot", incoming._tcrdlot);
                cmd.AddParameterStringWithValue("@topexp", DateUtil.ToInternalDate(incoming._topexp, WinNemoLib.DateUtil.eLanguage.EN));
                cmd.AddParameterStringWithValue("@toplen", incoming._toplen);
                cmd.AddParameterStringWithValue("@smpemp", incoming._Smpemp);
                cmd.AddParameterStringWithValue("@smpshtxt", incoming._Smpshtxt);
                cmd.AddParameterStringWithValue("@shift", incoming._Shift);
                cmd.AddParameterStringWithValue("@topauthemp", incoming._topauthemp);
                cmd.AddParameterStringWithValue("@topreatxt", incoming._topreatxt);
                cmd.AddParameterStringWithValue("@topshtxt", incoming._topshtxt);   
  
            }

            cmd.AddParameterStringWithValue("@ModifyBy", usernm);

            if (cmd.ExecuteNonQuery() <= 0)
            {
               cnn.RollbackTransaction();
               return false;
            }

            return true;
         }
         catch (Exception ex)
         {
            return false;
            throw ex;
         }

      }

      public List<BIASPLAN_DS> GET_TOPCORDPLN_ACT(string plandt = "", string top = "",string biasno="",string status="A",string item=""
          ,Boolean blnSkip=false,Boolean NoFinish=true)
      {
         string where = " where ";
         string ConnectionString = Nemo.GetConfiguredDbConnectionWrappertostring("Database");
         SqlConnection cnn = new SqlConnection(ConnectionString);
         StringBuilder str = new StringBuilder(" select ");

         if (StrUtil.IsNotEmpty(top)) {
         str.AppendLine("  Top " + top);
         }
         str.AppendLine(" case  when t.p1 is null then 0 else CEILING(t.p1/220)-isnull(nP1,0) end cP1, ");
         str.AppendLine(" case  when t.p2 is null then 0 else CEILING(t.p2/220)-isnull(nP2,0) end cP2, ");
         str.AppendLine(" case  when t.p3 is null then 0 else CEILING(t.p3/220)-isnull(nP3,0) end cP3, ");
         str.AppendLine(" case  when t.p4 is null then 0 else CEILING(t.p4/220)-isnull(nP4,0) end cP4 ,(nP1+nP2+nP3+nP4) act  ");
         str.AppendLine(" ,* from " + Nemo.GetINI("Dbase", "Base") + ".dbo.BIASPLAN t");
         str.AppendLine(" LEFT JOIN (");
         str.AppendLine(" SELECT biasno,plandt,itemno ");
         str.AppendLine(" ,sum(case when ply1='P1' then 1 else 0 end)+sum(case when ply2='P1' then 1 else 0 end) nP1");
         str.AppendLine(" ,sum(case when ply1='P2' then 1 else 0 end)+sum(case when ply2='P2' then 1 else 0 end) nP2");
         str.AppendLine(" ,sum(case when ply1='P3' then 1 else 0 end)+sum(case when ply2='P3' then 1 else 0 end) nP3");
         str.AppendLine(" ,sum(case when ply1='P4' then 1 else 0 end)+sum(case when ply2='P4' then 1 else 0 end) nP4");
         str.AppendLine(" FROM " + Nemo.GetINI("Dbase", "Base") + ".dbo.BIASSUBPLAN");
         str.AppendLine(" group by  biasno,plandt,itemno");
         str.AppendLine(" )b on t.biasno=b.biasno and t.plandt=b.plandt and  t.itemno=b.itemno");

         if (NoFinish)
         {
             str.AppendLine(where + " t.finflag is null  ");
             where = " and ";
         }

         if (status == "A")
         {
            str.AppendLine(where + " actflag='X'  ");
            where = " and ";
         }
         else if (status == "L")
         {
            str.AppendLine(where + "  Plansts='L'  ");
            where = " and ";
         }
         if (blnSkip)
         {
             str.AppendLine(where + "Skpflag is null");
             where = " and ";

         }

         if (StrUtil.IsNotEmpty(plandt))
         {
            str.AppendLine(where + "  t.plandt=convert(datetime,@plndt,120) ");
            where = " and ";
         }

         if (StrUtil.IsNotEmpty(biasno))
         {
            str.AppendLine(where + "  t.biasno=@biasno");
            where = " and ";
         }
         if (StrUtil.IsNotEmpty(item))
         {
            str.AppendLine(where + "  t.itemno=@itemno");
            where = " and ";
         }

         string orderby = " order by t.plandt,convert(int,t.itemno)  ";
         if (top == "15")
         {
             orderby = "  order by t.plandt desc,convert(int,t.itemno) desc";
         }

         SqlCommand cmd = new SqlCommand(str.ToString() + orderby, cnn);
         cmd.Connection.Open();
         cmd.CommandType = CommandType.Text;



         if (StrUtil.IsNotEmpty(plandt)) cmd.Parameters.Add("@plndt", SqlDbType.VarChar).Value = DateUtil.ToInternalDate(plandt, WinNemoLib.DateUtil.eLanguage.EN);
         if (StrUtil.IsNotEmpty(biasno)) cmd.Parameters.Add("@biasno", SqlDbType.VarChar).Value = biasno.Trim();
         if (StrUtil.IsNotEmpty(item)) cmd.Parameters.Add("@itemno", SqlDbType.VarChar).Value = item.Trim();
         SqlDataAdapter my = new SqlDataAdapter();
         DataSet ds = new DataSet();
         my.SelectCommand = cmd;
         my.Fill(ds);

         List<BIASPLAN_DS> list = new List<BIASPLAN_DS>();
         if (ds.Tables[0].Rows.Count <= 0)
         {
            cmd.Connection.Close();
            return list;
         }
         for (int cuIndex = 0; cuIndex < ds.Tables[0].Rows.Count; cuIndex++)
         {
            DataRow items = ds.Tables[0].Rows[cuIndex];
            BIASPLAN_DS model = new BIASPLAN_DS();
            model._Biasno = items["biasno"].ToString();
            model._Itemno = items["itemno"].ToString();
            model._Topcrdcd = items["topcrdcd"].ToString();
            model._Size = items["sizes"].ToString();
            model._Plants = items["Plansts"].ToString();
            model._Plandt = DateUtil.ToDisplayDate(items["plandt"]);
            model._Shift = items["shift"].ToString();
            model._P1 = items["P1"].ToString();
            model._P2 = items["P2"].ToString();
            model._P3 = items["P3"].ToString();
            model._P4 = items["P4"].ToString();
            model._Uptdat = DateUtil.ToDisplayDateTime(items["Uptdat"],DateUtil.eLanguage.EN);
            model._UpTemp = items["Uptemp"].ToString();
            model._Forming = items["forming"].ToString();
            model._Text = items["text"].ToString();
            model._toproll = items["toproll"].ToString();
            model._tcrdlot = items["tcrdlot"].ToString();
            model._toplen = items["toplen"].ToString();
            model._topexp = DateUtil.ToDisplayDate(items["topexp"], DateUtil.eLanguage.EN);
            model.cP1 = items["cP1"].ToString();
            model.cP2 = items["cP2"].ToString();
            model.cP3 = items["cP3"].ToString();
            model.cP4 = items["cP4"].ToString();
            model._Skpflag = items["Skpflag"].ToString();
            model._Actflag=items["act"].ToString();
            model._nfinemp = items["nfinemp"].ToString();
            model._Smpemp = items["smpemp"].ToString();
             
            //model._Angel = items["angPly1"].ToString();
            list.Add(model);
         }
         return list;
      }

      public List<BIASPLAN_DS> GET_NOT_FINISH(string plandt = "",string biasno="")
      {
         string where = " and ";
         string ConnectionString = Nemo.GetConfiguredDbConnectionWrappertostring("Database");
         SqlConnection cnn = new SqlConnection(ConnectionString);
         StringBuilder str = new StringBuilder();

         str.AppendLine(" select * from " + Nemo.GetINI("Dbase", "Base") + ".dbo.BIASPLAN t");
         str.AppendLine(" Where actflag='X' AND isnull(finflag,'')='' ");
         str.AppendLine(" and biasno= " + biasno);
         if (StrUtil.IsNotEmpty(plandt))
         {
            str.AppendLine(where + "  plandt=convert(datetime,@plndt,120) ");
            where = " and ";
         }



         SqlCommand cmd = new SqlCommand(str.ToString() + " order by plandt,convert(int,itemno) ", cnn);
         cmd.Connection.Open();
         cmd.CommandType = CommandType.Text;


         if (plandt != "") cmd.Parameters.Add("@plndt", SqlDbType.VarChar).Value = DateUtil.ToInternalDate(plandt, WinNemoLib.DateUtil.eLanguage.EN);

         SqlDataAdapter my = new SqlDataAdapter();
         DataSet ds = new DataSet();
         my.SelectCommand = cmd;
         my.Fill(ds);

         List<BIASPLAN_DS> list = new List<BIASPLAN_DS>();
         if (ds.Tables[0].Rows.Count <= 0)
         {
            cmd.Connection.Close();
            return list;
         }
         for (int cuIndex = 0; cuIndex < ds.Tables[0].Rows.Count; cuIndex++)
         {
            DataRow items = ds.Tables[0].Rows[cuIndex];
            BIASPLAN_DS model = new BIASPLAN_DS();
            model._Biasno = items["biasno"].ToString();
            model._Itemno = items["itemno"].ToString();
            model._Topcrdcd = items["topcrdcd"].ToString();
            model._Size = items["sizes"].ToString();
            model._Forming = items["Forming"].ToString();
            model._Plants = items["Plansts"].ToString();
            model._Plandt = DateUtil.ToDisplayDate(items["plandt"], DateUtil.eLanguage.EN);
            model._P1 = items["P1"].ToString();
            model._P2 = items["P2"].ToString();
            model._P3 = items["P3"].ToString();
            model._P4 = items["P4"].ToString();
            model._Uptdat = DateUtil.ToDisplayDateTime(items["Uptdat"], DateUtil.eLanguage.EN);
            model._UpTemp = items["Uptemp"].ToString();
            model._Text = items["text"].ToString();
            model._toproll = items["toproll"].ToString();
            model._tcrdlot = items["tcrdlot"].ToString();
            model._toplen = items["toplen"].ToString();
            model._topexp = DateUtil.ToDisplayDate(items["topexp"], DateUtil.eLanguage.EN);
            model._Actflag = items["actflag"].ToString();          
            model._Finflag = items["finflag"].ToString();
            model._Shift = items["shift"].ToString();
            model._Smpemp = items["smpemp"].ToString();

            list.Add(model);
         }
         return list;
      }

      public Boolean UPDATE(string usernm, string Status = "", string SEQ = "-", string dtPlan = "",string biasno="")
      {
         try
         {
            using (ConnectionWrapper cnn = Nemo.GetDefaultConfiguredDbConnectionWrapper())
            {
               cnn.Open();
               cnn.BeginTransaction();

               StringBuilder comstr = new StringBuilder();
               comstr.AppendLine(" UPDATE ");
               comstr.AppendLine(Nemo.GetINI("Dbase", "Base") + ".[dbo].[BIASPLAN] SET");
               if (Status == "A")
               {
                   comstr.AppendLine("  actflag='X' , actdt=getdate()  ,[empname]= @ModifyBy");
               }
               else if (Status == "R")
               {
                  comstr.AppendLine(" runflag='X' , Rundat=getdate() ");
               }
               else if (Status == "F")
               {
                  comstr.AppendLine(" finflag='X' , Findat=getdate() ");
               }
               else if ( (Status == "L") ||  (Status == "U"))
               {
                  comstr.AppendLine("Plansts='" + Status + "'");
                  comstr.AppendLine(",[Uptemp]= @ModifyBy, [Uptdat]=getdate() ");
               }

               if (Status == "L")
               {
                  comstr.AppendLine(" where Plansts='U' and isnull(actflag,'')='' and plandt = convert(datetime,@plndt,120) ");
               }
               if (Status == "U")
               {
                  string[] seqs = SEQ.Split(new string[] { "-" }, StringSplitOptions.RemoveEmptyEntries);
                  comstr.AppendLine(" where Plansts='L' and isnull(actflag,'')='' ");
                  comstr.AppendLine(" and  (convert(float,itemno) >= convert(float," + seqs[1] + ")");
                  comstr.AppendLine(" and plandt = convert(datetime,@plndt,120) ) ");
               }
               if ((Status == "A") || (Status == "R") || (Status == "F"))
               {
                  string[] seqs = SEQ.Split(new string[] { "-" }, StringSplitOptions.RemoveEmptyEntries);
                  comstr.AppendLine(" where Plansts='L' and plandt = convert(datetime,@plndt,120) ");
                  comstr.AppendLine(" and  convert(float,itemno) = convert(float," + seqs[1] + ")");

               }
               comstr.AppendLine(" and biasno= " + biasno);
               CommandWrapper cmd = cnn.CreateCommand();
               cmd.CommandText = comstr.ToString();
               if (Status == "U" || Status == "A" || (Status == "R") || Status == "F")
               {
                  string[] seqs = SEQ.Split(new string[] { "-" }, StringSplitOptions.RemoveEmptyEntries);
                  cmd.AddParameterStringWithValue("@plndt", DateUtil.ToInternalDate(seqs[0], WinNemoLib.DateUtil.eLanguage.EN));
               }
               else if (Status == "L")
               {
                  cmd.AddParameterStringWithValue("@plndt", DateUtil.ToInternalDate(dtPlan, WinNemoLib.DateUtil.eLanguage.EN));
               }

               cmd.AddParameterStringWithValue("@ModifyBy", usernm);
               if (cmd.ExecuteNonQuery() <= 0)
               {
                   cnn.RollbackTransaction();
                   return false;
               }

               if (Status == "A")
               {
                   //comstr = new StringBuilder();
                   //comstr.AppendLine(" INSERT ");
                   //comstr.AppendLine(Nemo.GetINI("Dbase", "LOG") + ".[dbo].[BIASPLAN] ");
                   //comstr.AppendLine(" SELECT * from");
                   //comstr.AppendLine(Nemo.GetINI("Dbase", "Base") + ".[dbo].[BIASPLAN] ");
                   //string[] seqs = SEQ.Split(new string[] { "-" }, StringSplitOptions.RemoveEmptyEntries);
                   //comstr.AppendLine(" where Plansts='L' and plandt = convert(datetime,@plndt,120) ");
                   //comstr.AppendLine(" and  convert(float,itemno) = convert(float," + seqs[1] + ")");
                   //comstr.AppendLine(" and biasno= " + biasno);
                   //cmd = cnn.CreateCommand();
                   //cmd.CommandText = comstr.ToString();
               
                   //cmd.AddParameterStringWithValue("@plndt", DateUtil.ToInternalDate(seqs[0], WinNemoLib.DateUtil.eLanguage.EN));
                   //cmd.ExecuteNonQuery();
               }

               cnn.CommitTransaction();
               return true;
            }
         }
         catch (Exception)
         {

            return false;
         }

      }

      public Boolean UPDATE_FIN_SAMP(ConnectionWrapper cnn,string usernm, string SEQ = "-", string dtPlan = "", string biasno = "")
      {
          try
          {
              StringBuilder comstr = new StringBuilder();
              comstr.AppendLine(" UPDATE ");
              comstr.AppendLine(Nemo.GetINI("Dbase", "Base") + ".[dbo].[BIASPLAN]");

              comstr.AppendLine(" SET findat=getdate(),finflag='X' ");
              
              comstr.AppendLine(" where plandt=convert(datetime,@plndt,120) and biasno=@biasno ");
              comstr.AppendLine(" and itemno=@itemno ");

            CommandWrapper cmd = cnn.CreateCommand();
            cmd.CommandText = comstr.ToString();
            string[] seqs = SEQ.Split(new string[] { "-" }, StringSplitOptions.RemoveEmptyEntries);
            cmd.AddParameterStringWithValue("@plndt", DateUtil.ToInternalDate(seqs[0], WinNemoLib.DateUtil.eLanguage.EN));
            cmd.AddParameterStringWithValue("@biasno", biasno);
            cmd.AddParameterStringWithValue("@itemno",seqs[1]);      
              

              if (cmd.ExecuteNonQuery() <= 0)
              {
                  return false;
              }

              return true;
          }
          catch (Exception ex)
          {
              return false;
              throw ex;
          }

      }

      public Boolean ROLLBACK_STATUS(string usernm, string Status = "", string SEQ = "-",string biasno="")
      {
         try
         {
            using (ConnectionWrapper cnn = Nemo.GetDefaultConfiguredDbConnectionWrapper())
            {
               cnn.Open();
               cnn.BeginTransaction();

               StringBuilder comstr = new StringBuilder();
               comstr.AppendLine(" UPDATE ");
               comstr.AppendLine(Nemo.GetINI("Dbase", "Base") + ".[dbo].[BIASPLAN] SET");
               if (Status == "1")
               {
                  //comstr.AppendLine(" [empname]=@empname,");
                  comstr.AppendLine("[Tcrdlot]=null,");
                  comstr.AppendLine("[toproll]=null,");
                  comstr.AppendLine("[topexp]=null ,");
                  comstr.AppendLine("[toplen]=null,");
                  comstr.AppendLine("[topauthemp]=null,");
                  comstr.AppendLine("[topreatxt]=null,[topshtxt]=null ");
                  comstr.AppendLine(",actdt=null,actflag=null ");
                  comstr.AppendLine("where biasno=@biasno and plandt = convert(datetime,@plandt,120) and itemno=@itemno");

               }
               else if (Status == "0")//Header_barcode
               {                  
 
                   comstr.AppendLine("actdt=null,actflag=null ");
                   comstr.AppendLine("where biasno=@biasno and plandt = convert(datetime,@plandt,120) and itemno=@itemno");

               }


               CommandWrapper cmd = cnn.CreateCommand();
               cmd.CommandText = comstr.ToString();
               if (Status == "1" || Status == "0")
               {
                  string[] seqs = SEQ.Split(new string[] { "-" }, StringSplitOptions.RemoveEmptyEntries);
                  cmd.AddParameterStringWithValue("@plandt", DateUtil.ToInternalDate(seqs[0], WinNemoLib.DateUtil.eLanguage.EN));
                  cmd.AddParameterStringWithValue("@biasno", biasno);
                  cmd.AddParameterStringWithValue("@itemno", seqs[1]);
               }

               //cmd.AddParameterStringWithValue("@ModifyBy", usernm);
               
               if (cmd.ExecuteNonQuery() <= 0)
               {
                  cnn.RollbackTransaction();
                  return false;
               }

               cnn.CommitTransaction();
               return true;
            }
         }
         catch (Exception)
         {

            return false;
         }

      }

      public Boolean DEL_NEW_SAMPLE(string SEQ = "-", string biasno = "")
      {
          try
          {
              using (ConnectionWrapper cnn = Nemo.GetDefaultConfiguredDbConnectionWrapper())
              {
                  cnn.Open();
                  cnn.BeginTransaction();

                  StringBuilder comstr = new StringBuilder();
                  comstr.AppendLine(" DELETE ");
                  comstr.AppendLine(Nemo.GetINI("Dbase", "Base") + ".[dbo].[BIASPLAN] ");


                  comstr.AppendLine("where biasno=@biasno and plandt = convert(datetime,@plandt,120) and itemno=@itemno");
                  CommandWrapper cmd = cnn.CreateCommand();
                  cmd.CommandText = comstr.ToString();
                 
                      string[] seqs = SEQ.Split(new string[] { "-" }, StringSplitOptions.RemoveEmptyEntries);
                      cmd.AddParameterStringWithValue("@plandt", DateUtil.ToInternalDate(seqs[0], WinNemoLib.DateUtil.eLanguage.EN));
                      cmd.AddParameterStringWithValue("@biasno", biasno);
                      cmd.AddParameterStringWithValue("@itemno", seqs[1]);
                  

                  //cmd.AddParameterStringWithValue("@ModifyBy", usernm);

                  if (cmd.ExecuteNonQuery() <= 0)
                  {
                      cnn.RollbackTransaction();
                      return false;
                  }

                  cnn.CommitTransaction();
                  return true;
              }
          }
          catch (Exception)
          {

              return false;
          }

      }
   }
}